package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modele.Formation;
import modele.Theme;

public class FormationThemeDAO {

	private static final String	ID_FORMATION					= "id_formation";
	private static final String	ID_THEME						= "id_theme";

	private static final String	TABLE							= "`agriotes`.`formation_theme`";

	static final String			SQL_INSERER						= "INSERT INTO " + TABLE + " (" + ID_FORMATION + "," + ID_THEME + ") VALUES (?, ?); ";

	static final String			SQL_SUPPRIMER					= "DELETE FROM " + TABLE + " WHERE ID_FORMATION = ? ;";

	static final String			SQL_LISTE_THEME					= "SELECT * FROM " + TABLE + " WHERE " + ID_FORMATION + "=?";
	static final String			SQL_LISTE_FORMATION_BY_THEME	= "SELECT * FROM " + TABLE + " WHERE " + ID_THEME + " IN(?)";

	public static PreparedStatement getPreparedStatement(String sql) throws SQLException {

		return getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
	}

	public static PreparedStatement getPreparedStatement(String sql, int autoGeneratedKeys) throws SQLException {

		Connection connection = FabriqueDAO.getConnection();
		return connection.prepareStatement(sql, autoGeneratedKeys);

	}

	/**
	 * méthode pour créer une nouvelle formation dans la base de données
	 * 
	 * @param f
	 */
	public static void inserer(Formation f, Theme t) throws SQLException {

		PreparedStatement pt = getPreparedStatement(SQL_INSERER, Statement.RETURN_GENERATED_KEYS);

		pt.setInt(1, f.getIdFormation());
		pt.setInt(2, t.getId_theme());

		pt.executeUpdate();
		pt.close();

	}

	/**
	 * méthode pour effacer une formation de la base de données selon son nom
	 * 
	 * @param id
	 */
	public static void supprimer(int id) throws SQLException {

		PreparedStatement pt = getPreparedStatement(SQL_SUPPRIMER);
		pt.setInt(1, id);
		pt.executeUpdate();

		// close
		pt.close();

	}

	public static ArrayList<Theme> getFormationThemes(Formation f) throws SQLException {
		
		ArrayList<Theme> tableauTheme = new ArrayList<>();
		
		PreparedStatement pt = getPreparedStatement(SQL_LISTE_THEME);
		pt.setInt(1, f.getIdFormation());
		ResultSet rs = pt.executeQuery();
		
		ThemeDAO themeDao = new ThemeDAO();
		
		while (rs.next()) {
			
			int idTheme = rs.getInt(ID_THEME);
			
			tableauTheme.add(themeDao.lireParId(idTheme));
		}
		
		// close
		rs.close();
		pt.close();
		
	
		
		return tableauTheme;
		
	}
	public static ArrayList<Formation> getFormationByThemes(String themesIds) throws SQLException {

		ArrayList<Formation> tableauFormation = new ArrayList<>();

		PreparedStatement pt = getPreparedStatement(SQL_LISTE_FORMATION_BY_THEME);

		pt.setString(1, themesIds);
		ResultSet rs = pt.executeQuery();

		while (rs.next()) {

			int id = rs.getInt(ID_FORMATION);
			tableauFormation.add(FormationDAO.lireParId(id));
		}

		// close
		rs.close();
		pt.close();

		return tableauFormation;

	}
}
